import xlrd
import pymysql

# Excel 文件路径
EXCEL_PATH = r"D:\软件测试\微信文件\python基础部分和单元测试的开发\5.baidu-员工的人员信息.xls"

# MySQL 连接配置
MYSQL_CONFIG = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "root",
    "db": "report",
    "charset": "utf8mb4"
}

def excu(sql, param):
    # 执行 SQL 语句
    try:
        # 连接数据库
        con = pymysql.connect(**MYSQL_CONFIG)
        cursor = con.cursor()
        # 执行 SQL
        cursor.executemany(sql, param)
        # 提交事务
        con.commit()
        print(f"成功插入 {len(param)} 条数据！")

    except Exception as e:
        print(f"发生错误: {e}")
        con.rollback()
    finally:
        # 关闭资源
        cursor.close()
        con.close()

def read_excel_data(file_path):
    """
    读取 Excel 文件中的数据
    """
    try:
        # 打开 Excel 文件
        workbook = xlrd.open_workbook(file_path)
        sheet = workbook.sheet_by_index(0)

        # 读取数据
        data = []
        for row_index in range(1, sheet.nrows):  # 跳过表头行
            row = sheet.row_values(row_index)
            data.append(row)

        print(f"成功读取 {len(data)} 条数据！")
        return data

    except Exception as e:
        print(f"读取 Excel 文件时发生错误: {e}")
        return []

if __name__ == '__main__':
    # SQL 语句
    sql = 'INSERT INTO user_info (id,idcard,username,realname,pwd,telphone,email,age,sex,address,hiredate,sal,job,company) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'

    # 读取 Excel 数据
    param = read_excel_data(EXCEL_PATH)

    # 将数据插入到 MySQL 数据库
    if param:
        excu(sql, param)